﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace JamesThew.Dashboard
{
    public class PostsCategoriesDB
    {

        public Boolean Update(int PostID, String ListCategoryID)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            cmd.CommandText = "DELETE FROM CMS_Posts_Categories WHERE PostID = " + PostID;

            String[] ListCategory = ListCategoryID.Split(',');
            String query = "";
            foreach (String s in ListCategory)
            {
                query += "INSERT INTO CMS_Posts_Categories(PostID, CategoryID) VALUES(" + PostID + ", " + s + ");";
            }
            cmd.CommandText += query;

            Int32 check = cmd.ExecuteNonQuery();
            con.Close();
            if (check > 0) return true;
            return false;
        }

        public String GetListCatOfPost(int PostID, String format)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT CategoryID FROM CMS_Posts_Categories WHERE PostID = " + PostID;
            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            String CatList = "";

            try
            {

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (format == "text")
                    {
                        CatList += this.GetCategoryName((int)dt.Rows[i][0]) + ",";
                    }
                    else
                    {
                        CatList += (int)dt.Rows[i][0] + ",";
                    }
                }

                if (CatList != "")
                {
                    CatList = CatList.TrimEnd(',');
                }
            }
            catch (NullReferenceException)
            {

            }
            con.Close();

            return CatList;
        }

        public String GetCategoryName(int CatID)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT CategoryName FROM CMS_Categories WHERE CategoryID = " + CatID;
            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            String CatName = "";
            try
            {
                CatName = dt.Rows[0][0].ToString();
            }
            catch (IndexOutOfRangeException ex)
            {
                
            }
            con.Close();

            return CatName;
        }

        public String GetListPostOfCat(int CatID)
        {

            return "";
        }
    }
}